package com.choudou5.solr.util.jdbc;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ArrayUtil;
import com.choudou5.solr.util.StrUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.util.*;

/**
 * @Name：SQL格式化 工具类
 * @@author choudou5
 * @@date 2018-07-14
 */
public class SqlFormatUtil {

    private static final Logger logger = LoggerFactory.getLogger(SqlFormatUtil.class);

    public static void main(String[] args) {
        String sql = "SELECT * FROM ? WHERE phone = ?";
        sql = format(sql, "solr_sys_user", 13632676703L);
        System.out.println(sql);

        Map<String, Object> params = new HashMap();
        params.put("id", 11);
        params.put("title", "李四");
        params.put("time", new Date());
        sql = buildInsertSql("solr_sys_test", params);
        System.out.println(sql);
    }

    private static boolean isIgnoreField(String field){
        return field.equals("createByName") || field.equals("new");
    }

    /**
     * 格式化 SQL
     * @param sqlTpl sql模板 参数用 ?
     * @param params 参数值
     * @return
     */
    public static String format(String sqlTpl, Object ... params) {
        if(ArrayUtil.isEmpty(params)){
            return sqlTpl;
        }
        StringBuilder sqlStr = new StringBuilder();
        char[] chars = sqlTpl.toCharArray();
        int dex = 0;
        Object param = null;
        for (char c : chars) {
            if(c == '?'){
                param = params[dex];
                sqlStr.append(wrapValToStr(param));
                dex++;
            }else{
                sqlStr.append(c);
            }
        }
        return sqlStr.toString();
    }


    /**
     * 构建 updateById SQL
     * @param table
     * @param params
     * @param ignoreNull 忽略空
     * @return
     */
    public static String buildUpdateByIdSql(String table, Map<String, Object> params, boolean ignoreNull) throws SQLException {
        Object id = params.get("id");
        if(id == null)
            throw new SQLException(table+" 表无 id字段，构建sql失败");
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE "+table+" SET");
        int dex = 0;
        Object val = null;
        for (String field : params.keySet()) {
            val = params.get(field);
            //忽略空值
            if(ignoreNull && val == null)
                continue;
            if(!"id".equals(field) && !isIgnoreField(field)){
                sql.append((dex>0?", ":"")+ " `"+ StrUtil.toSqlField(field)+"` = " + wrapValToStr(val));
                dex++;
            }
        }
        sql.append(" WHERE id = "+id);
        return sql.toString();
    }


    /**
     * 构建 插入语句
     * @param table
     * @param params
     * @return
     */
    public static String buildInsertSql(String table, Map<String, ?> params) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO "+table+" (");
        int dex = 0;
        //设置 字段
        for (String field : params.keySet()) {
            if(params.get(field) != null && !isIgnoreField(field)) {
                sql.append((dex > 0 ? ", " : "") + " `" + StrUtil.toUnderlineCase(field) + "`");
                dex++;
            }
        }

        dex = 0;
        //设置 字段值
        sql.append(") VALUES (");
        Object val = null;
        for (String field : params.keySet()) {
            val = params.get(field);
            if(val != null && !isIgnoreField(field)){
                sql.append((dex>0?", ":"") + wrapValToStr(val));
                dex++;
            }
        }
        sql.append(");");
        return sql.toString();
    }


    /**
     * 包装 参数
     * @param params
     * @return
     */
    public static Map<String, ?> wrapParams(Map<String, ?> params) {
        Map<String, Object> newParams = new HashMap<>();
        for (String field : params.keySet()) {
            newParams.put(StrUtil.toUnderlineCase(field), wrapValToStr(params.get(field)));
        }
        return newParams;
    }


    private static String wrapValToStr(Object param){
        if(param instanceof String){
            if(param.toString().startsWith("`solr_")) //兼容表名
                return param.toString();
            return "'"+param.toString()+"'";
        }else if(param instanceof Date){
            return "'" + DateUtil.formatDateTime((Date)param) + "'";
        }else{
            return param==null?null:param.toString();
        }
    }

    /**
     * 获取 SQL语句 里的 SQL映射字段
     * @param sql
     * @return
     */
    public static List<String> getSqlStatementSqlFields(String sql){
        List<String> fields = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        String fieldArrayStr = StringUtils.substringBetween(sql.toLowerCase(), "select", "from");
        if(StrUtil.isNotBlank(fieldArrayStr)){
            String[] array = fieldArrayStr.split(",");
            StringBuilder field = null;
            for (int i = 0; i < array.length; i++) {
                String fieldStr = array[i].trim();
                field = new StringBuilder();
                int symbol = 0;
                for (int j = fieldStr.length()-1; j >= 0 && symbol < 2; j--) {
                    char c = fieldStr.charAt(j);
//                    System.out.println(c+","+((int)c));
                    if(c == 96) { //`
                        symbol++;
                        continue;
                    }
                    if(c == 32) { //空格
                        break;
                    }
                    field.append(c);
                }
                fields.add(field.reverse().toString());
            }
        }
        return fields;
    }

}
